package edu.mtu.citizenscience.ethnography.database;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.text.DateFormat;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

class InterviewDataSource {
	private static final String LOG_TAG = "InterviewDataSource";

	private SQLiteDatabase db;
	private SQLiteOpenHelper helper;
	private String[] allInterviewColumns = { EthnographySQLiteOpenHelper.COLUMN_ID,
			EthnographySQLiteOpenHelper.COLUMN_STORY_TITLE,
			EthnographySQLiteOpenHelper.COLUMN_SUB_FIRST_NAME,
			EthnographySQLiteOpenHelper.COLUMN_SUB_LAST_NAME,
			EthnographySQLiteOpenHelper.COLUMN_INTER_FIRST_NAME,
			EthnographySQLiteOpenHelper.COLUMN_INTER_LAST_NAME,
			EthnographySQLiteOpenHelper.COLUMN_LOCATION_NAME,
			EthnographySQLiteOpenHelper.COLUMN_YEAR,
			EthnographySQLiteOpenHelper.COLUMN_MONTH,
			EthnographySQLiteOpenHelper.COLUMN_DAY,
			EthnographySQLiteOpenHelper.COLUMN_NOTES,
			EthnographySQLiteOpenHelper.COLUMN_MEDIA_PATH,
			EthnographySQLiteOpenHelper.COLUMN_UPLOADED,
			EthnographySQLiteOpenHelper.COLUMN_SERVER_KEY};

	private String[] allAudioColumns = { EthnographySQLiteOpenHelper.COLUMN_FILE_NAME,
			EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID,
			EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE,
			EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE,
			EthnographySQLiteOpenHelper.COLUMN_END_LATITUDE,
			EthnographySQLiteOpenHelper.COLUMN_END_LONGITUDE,
			EthnographySQLiteOpenHelper.COLUMN_YEAR,
			EthnographySQLiteOpenHelper.COLUMN_MONTH,
			EthnographySQLiteOpenHelper.COLUMN_DAY,
			EthnographySQLiteOpenHelper.COLUMN_HOUR,
			EthnographySQLiteOpenHelper.COLUMN_MINUTE,
			EthnographySQLiteOpenHelper.COLUMN_SECOND,
			EthnographySQLiteOpenHelper.COLUMN_UPLOADED};

	private String[] allPhotoColumns = { EthnographySQLiteOpenHelper.COLUMN_FILE_NAME,
			EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID,
			EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE,
			EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE,
			EthnographySQLiteOpenHelper.COLUMN_YEAR,
			EthnographySQLiteOpenHelper.COLUMN_MONTH,
			EthnographySQLiteOpenHelper.COLUMN_DAY,
			EthnographySQLiteOpenHelper.COLUMN_HOUR,
			EthnographySQLiteOpenHelper.COLUMN_MINUTE,
			EthnographySQLiteOpenHelper.COLUMN_SECOND,
			EthnographySQLiteOpenHelper.COLUMN_UPLOADED};

	InterviewDataSource(Context context) {
		helper = new EthnographySQLiteOpenHelper(context);
		db = null;
	}

	/**
	 * Makes the database available for operations, including reading and writing.
	 * 
	 * After being called once, it may be called any number of times before a single
	 * call to {@link InterviewDataSource#close() close()}.
	 * After a single call to {@link InterviewDataSource#close() close()}, 
	 * this method must be called again before database operations may be performed.
	 * 
	 * @throws SQLiteException If the database cannot be opened
	 */
	void open() throws SQLiteException {
		try {
			db = helper.getWritableDatabase();
		} catch (SQLiteException e) {
			Log.e(LOG_TAG, "Attempting to open the database generated an exception", e);
			db = null;
			throw e;
		}

	}

	/**
	 * Closes the database, making it unavailable for operations.
	 */
	void close() {
		helper.close();
		db = null;
	}

	/**
	 * Returns whether the database is available for operations. Attempting
	 * to read from or write to the database if the database is not available
	 * will fail and generate an exception.
	 * 
	 * The database is available between a successful call to 
	 * {@link InterviewDataSource#open() open()} and a successful call to
	 * {@link InterviewDataSource#close() close()}.
	 * 
	 * @return {@code true} if the database is open, {@code false} if not.
	 */
	boolean isAvailable() {
		return db != null;
	}

	/**
	 * Queries the database for a single instance of Interview by its numeric identifier, 
	 * then returns that interview as an object. May return {@code null} if an error occurs
	 * parsing the database contents.
	 * 
	 * @param id The numeric identifier for the interview
	 * @return An {@link Interview} object containing the interview.
	 * @throws IllegalStateException if the database is not open.
	 */
	Interview getInterviewByID(int id) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		String selectionArgs[] = new String[1];
		selectionArgs[0] = Integer.toString(id);
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS,
				allInterviewColumns,
				EthnographySQLiteOpenHelper.COLUMN_ID + " = ?",
				selectionArgs,
				null,
				null,
				null);

		Interview interview = null;
		if (cursor.getCount() == 0) {
			Log.i(LOG_TAG, "Database query retrieved no results");
		} else if (cursor.getCount() == 1){
			interview = new Interview();
			cursor.moveToFirst();
			try {
				populateInterview(interview, cursor);
			} catch (ParseException e) {
				throw new SQLiteException(e.getLocalizedMessage());
			}
		} else {
			Log.e(LOG_TAG, "Querying for single interview returned multiple results " +
					"on primary-key selection");
			cursor.close();
			throw new SQLiteConstraintException("Query returned multiple results " +
					"on primary-key selection");
		}

		cursor.close();

		return interview;
	}

	/**
	 * Queries the database for all interviews currently stored, and returns the results
	 * as an array of {@link Interview} objects. May return {@code null} if an error occurs
	 * parsing the database contents.
	 * 
	 * @return An array of {@link Interview Interviews} containing all rows in the database.
	 * @throws IllegalStateException if the database is not open.
	 */
	List<Interview> getAllInterviews() {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		List<Interview> interviews = null;
		Cursor cursor;

		cursor = db.query(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS,
				allInterviewColumns,
				null,
				null,
				null,
				null,
				null);

		int count = cursor.getCount();
		if (count > 0) {
			interviews = new ArrayList<Interview>(count);
			cursor.moveToFirst();
			for (int i = 0; i < count; i++) {
				Interview interview = new Interview();

				try {
					populateInterview(interview, cursor);
				} catch (ParseException e) {
					throw new SQLiteException(e.getLocalizedMessage());
				}
				interviews.add(interview);
			}
		} else {
			interviews = new ArrayList<Interview>(0);
		}

		cursor.close();
		return interviews;
	}

	/**
	 * Populates the contents of an {@link Interview} object with the fields
	 * of a cursor returned from a database query. The method populates from the
	 * current row and automatically moves to the next one. If the cursor's row
	 * was already beyond the last, this method does nothing
	 * @param interview The {@link Interview} object to populate
	 * @param cursor The {@link Cursor} to get data from
	 * @return {@code true} if, following this method, the current row is not
	 * beyond the last. {@code false} otherwise.
	 * @throws ParseException If the date stored in the database does not parse correctly
	 */
	private static boolean populateInterview(Interview interview, Cursor cursor) throws ParseException {
		if (!cursor.isAfterLast()) {
			interview.setID(cursor.getInt(0));
			interview.setStoryTitle(cursor.getString(1));
			interview.setSubjectName(new Interview.Name(cursor.getString(2), cursor.getString(3)));
			interview.setInterviewerName(new Interview.Name(cursor.getString(4), cursor.getString(5)));
			interview.setLocationName(cursor.getString(6));

			Date date;
			int year = cursor.getInt(7);
			int month = cursor.getInt(8);
			int day = cursor.getInt(9);
			if (year == -1 && month == -1 && day == -1) {
				date = null;
			} else {
				Calendar calendar = new GregorianCalendar();
				calendar.set(Calendar.YEAR, year);
				calendar.set(Calendar.MONTH, month);
				calendar.set(Calendar.DAY_OF_MONTH, day);
				date = calendar.getTime();
			}
			interview.setDate(date);

			interview.setNotes(cursor.getString(10));
			interview.setMediaPath(cursor.getString(11));
			interview.setUploaded((cursor.getInt(12) != 0));
			interview.setServerKey(cursor.getInt(13));
			cursor.moveToNext();
		}
		return cursor.isAfterLast();
	}

	/**
	 * Adds a blank row to the database, and returns the id of the new row.
	 * 
	 * @return The integer id of the new row.
	 * @throws IllegalStateException if the database is not open.
	 */
	int addEmptyInterview() {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		ContentValues values = valuesFromInterview(null);

		int id = (int)db.insert(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS, null, values);

		return id;
	}

	/**
	 * Adds a row to the database populated data from an {@link Interview} object and
	 * returns the id of the new row.
	 * @param interview The {@link Interview} object from which to populate the new
	 * row
	 * @return The integer id of the new row.
	 * @throws IllegalStateException if the database is not open.
	 * @throws IllegalArgumentException if {@code interview} is {@code null}
	 */
	int addInterview(Interview interview) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		if (interview == null) {
			throw new IllegalArgumentException("addInterview() passed null Interview");
		}

		ContentValues values = valuesFromInterview(interview);

		int id = (int) db.insert(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS, null, values);

		return id;
	}

	boolean addAudio(InterviewAudio audio) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		ContentValues values = valuesFromAudio(audio);

		int row_id = (int) db.insert(EthnographySQLiteOpenHelper.TABLE_AUDIO, null, values);

		return (row_id != -1);
	}

	private static ContentValues valuesFromAudio(InterviewAudio audio) {
		ContentValues values = new ContentValues();
		values.put(EthnographySQLiteOpenHelper.COLUMN_FILE_NAME, audio.getFileName());
		values.put(EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID, audio.getInterviewID());

		Location startLocation = audio.getStartLocation();
		if (startLocation != null) {
			values.put(EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE, startLocation.getLatitude());
			values.put(EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE, startLocation.getLongitude());
		} else {
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE);
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE);
		}
		
		Location endLocation = audio.getEndLocation();
		if (endLocation != null) {
			values.put(EthnographySQLiteOpenHelper.COLUMN_END_LATITUDE, endLocation.getLatitude());
			values.put(EthnographySQLiteOpenHelper.COLUMN_END_LONGITUDE, endLocation.getLongitude());
		} else {
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_END_LATITUDE);
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_END_LONGITUDE);
		}
		
		Date date = audio.getDate();
		if (date == null) {
			// Populate with "dummy" values (should never be null anyway though)
			values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_HOUR, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MINUTE, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_SECOND, -1);
		} else {
			Calendar calendar = new GregorianCalendar();
			calendar.setTime(date);
			values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, calendar.get(Calendar.YEAR));
			values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, calendar.get(Calendar.MONTH));
			values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, calendar.get(Calendar.DAY_OF_MONTH));
			values.put(EthnographySQLiteOpenHelper.COLUMN_HOUR, calendar.get(Calendar.HOUR_OF_DAY));
			values.put(EthnographySQLiteOpenHelper.COLUMN_MINUTE, calendar.get(Calendar.MINUTE));
			values.put(EthnographySQLiteOpenHelper.COLUMN_SECOND, calendar.get(Calendar.SECOND));
		}
		values.put(EthnographySQLiteOpenHelper.COLUMN_UPLOADED, audio.isUploaded());
		return values;
	}

	boolean addPhoto(InterviewPhoto photo) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		ContentValues values = valuesFromPhoto(photo);

		int row_id = (int) db.insert(EthnographySQLiteOpenHelper.TABLE_PHOTO, null, values);

		return (row_id != -1);
	}

	private static ContentValues valuesFromPhoto(InterviewPhoto photo) {
		ContentValues values = new ContentValues();
		values.put(EthnographySQLiteOpenHelper.COLUMN_FILE_NAME, photo.getFileName());
		values.put(EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID, photo.getInterviewID());
		
		Location location = photo.getLocation();
		if (location != null) {
			values.put(EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE, location.getLatitude());
			values.put(EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE, location.getLongitude());
		} else {
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_START_LATITUDE);
			values.putNull(EthnographySQLiteOpenHelper.COLUMN_START_LONGITUDE);
		}
		
		Date date = photo.getDate();
		if (date == null) {
			// Populate with "dummy" values (should never be null anyway though)
			values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_HOUR, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MINUTE, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_SECOND, -1);
		} else {
			Calendar calendar = new GregorianCalendar();
			calendar.setTime(date);
			values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, calendar.get(Calendar.YEAR));
			values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, calendar.get(Calendar.MONTH));
			values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, calendar.get(Calendar.DAY_OF_MONTH));
			values.put(EthnographySQLiteOpenHelper.COLUMN_HOUR, calendar.get(Calendar.HOUR_OF_DAY));
			values.put(EthnographySQLiteOpenHelper.COLUMN_MINUTE, calendar.get(Calendar.MINUTE));
			values.put(EthnographySQLiteOpenHelper.COLUMN_SECOND, calendar.get(Calendar.SECOND));
		}
		
		values.put(EthnographySQLiteOpenHelper.COLUMN_UPLOADED, photo.isUploaded());
		
		return values;
	}

	List<InterviewAudio> getAudioWithID(int ID) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		List<InterviewAudio> allAudio = null;
		String selectionArgs[] = new String[1];

		selectionArgs[0] = String.valueOf(ID);
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_AUDIO,
				allAudioColumns,
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?", 
				selectionArgs,
				null,
				null,
				null);

		int count = cursor.getCount();
		allAudio = new ArrayList<InterviewAudio>(count);
		if (count > 0) {
			cursor.moveToFirst();
			for (int i = 0; i < count; i++) {
				InterviewAudio audio = new InterviewAudio();
				populateAudio(audio, cursor);
				allAudio.add(audio);
			}
		}
		
		cursor.close();

		return allAudio;
	}
	
	InterviewAudio getAudioWithIDAndName(int id, String name) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		
		InterviewAudio audio = null;
		String selectionArgs[] = new String[2];

		selectionArgs[0] = String.valueOf(id);
		selectionArgs[1] = name;
		
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_AUDIO,
				allAudioColumns,
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ? AND " + 
						EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ?", 
				selectionArgs,
				null,
				null,
				null);
		
		if (cursor.getCount() == 1) {
			audio = new InterviewAudio();
			cursor.moveToFirst();
			populateAudio(audio, cursor);
		}
		
		cursor.close();
		
		return audio;
	}

	private static boolean populateAudio(InterviewAudio audio, Cursor cursor) {
		if (!cursor.isAfterLast()) {
			audio.setFileName(cursor.getString(0));
			audio.setInterviewID(cursor.getInt(1));
			
			Location startLocation;
			if (cursor.isNull(2) && cursor.isNull(3)) {
				startLocation = null;
			} else {
				startLocation = new Location("InterviewDataService");
				startLocation.setLatitude(cursor.getDouble(2));
				startLocation.setLongitude(cursor.getDouble(3));
			}
			audio.setStartLocation(startLocation);
			
			Location endLocation;
			if (cursor.isNull(4) && cursor.isNull(5)) {
				endLocation = null;
			} else {
				endLocation = new Location("InterviewDataService");
				endLocation.setLatitude(cursor.getDouble(4));
				endLocation.setLongitude(cursor.getDouble(5));
			}
			audio.setEndLocation(endLocation);
			
			Calendar calendar = new GregorianCalendar();
			calendar.set(Calendar.YEAR, cursor.getInt(6));
			calendar.set(Calendar.MONTH, cursor.getInt(7));
			calendar.set(Calendar.DAY_OF_MONTH, cursor.getInt(8));
			calendar.set(Calendar.HOUR_OF_DAY, cursor.getInt(9));
			calendar.set(Calendar.MINUTE, cursor.getInt(10));
			calendar.set(Calendar.SECOND, cursor.getInt(11));
			audio.setDate(calendar.getTime());
			
			audio.setUploaded(cursor.getInt(12) != 0);
			
			cursor.moveToNext();
		}

		return cursor.isAfterLast();
	}

	List<InterviewPhoto> getPhotoWithID(int ID) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		List<InterviewPhoto> photos = null;
		String selectionArgs[] = new String[1];

		selectionArgs[0] = String.valueOf(ID);
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_PHOTO,
				allPhotoColumns,
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?", 
				selectionArgs,
				null,
				null,
				null);

		int count = cursor.getCount();
		photos = new ArrayList<InterviewPhoto>(count);
		if (count > 0) {
			cursor.moveToFirst();
			for (int i = 0; i < count; i++) {
				InterviewPhoto photo = new InterviewPhoto();
				populatePhoto(photo, cursor);
				photos.add(photo);
			}
		}
		
		cursor.close();
		
		return photos;
	}
	
	InterviewPhoto getPhotoWithIDAndName(int id, String name) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		InterviewPhoto photo = null;
		String selectionArgs[] = new String[2];

		selectionArgs[0] = String.valueOf(id);
		selectionArgs[1] = name;
		
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_PHOTO,
				allPhotoColumns,
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ? AND " + 
						EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ?", 
				selectionArgs,
				null,
				null,
				null);
		
		if (cursor.getCount() == 1) {
			photo = new InterviewPhoto();
			cursor.moveToFirst();
			populatePhoto(photo, cursor);
		}
		
		cursor.close();
		
		return photo;
	}

	private static boolean populatePhoto(InterviewPhoto photo, Cursor cursor) {
		if (!cursor.isAfterLast()) {
			photo.setFileName(cursor.getString(0));
			photo.setInterviewID(cursor.getInt(1));
			
			Location location;
			if (cursor.isNull(2) && cursor.isNull(3)) {
				location = null;
			} else {
				location = new Location("InterviewDataService");
				location.setLatitude(cursor.getDouble(2));
				location.setLongitude(cursor.getDouble(3));
			}
			photo.setLocation(location);
			
			Calendar calendar = new GregorianCalendar();
			calendar.set(Calendar.YEAR, cursor.getInt(4));
			calendar.set(Calendar.MONTH, cursor.getInt(5));
			calendar.set(Calendar.DAY_OF_MONTH, cursor.getInt(6));
			calendar.set(Calendar.HOUR_OF_DAY, cursor.getInt(7));
			calendar.set(Calendar.MINUTE, cursor.getInt(8));
			calendar.set(Calendar.SECOND, cursor.getInt(9));
			photo.setDate(calendar.getTime());
			
			photo.setUploaded(cursor.getInt(10) != 0);
			
			cursor.moveToNext();
		}

		return cursor.isAfterLast();
	}

	boolean deleteAudio(InterviewAudio audio) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		String whereClause = EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ? and " +
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String whereArgs[] = new String[2];
		whereArgs[0] = audio.getFileName();
		whereArgs[1] = String.valueOf(audio.getInterviewID());

		int rows = db.delete(EthnographySQLiteOpenHelper.TABLE_AUDIO, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Delete on primary-key restraint updated more than one row");
		} 
		return result;
	}

	boolean deletePhoto(InterviewPhoto photo) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}

		String whereClause = EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ? and " +
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String whereArgs[] = new String[2];
		whereArgs[0] = photo.getFileName();
		whereArgs[1] = String.valueOf(photo.getInterviewID());

		int rows = db.delete(EthnographySQLiteOpenHelper.TABLE_PHOTO, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Delete on primary-key restraint updated more than one row");
		} 
		return result;
	}

	/**
	 * Updates the database such that the row specified by the {@code ID} column in the
	 * {@code interview} object is updated to reflect the rest of the data in the {@code interview}
	 * object.
	 * 
	 * @param interview The data to update the table with
	 * @return {@code true} if the update succeeded. {@code false} if there is no
	 * such row in the database.
	 * @throws IllegalStateException if the database is not open.
	 * @throws IllegalArgumentException if {@code interview} is {@code null} 
	 */
	boolean updateInterview(Interview interview) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		if (interview == null) {
			throw new IllegalArgumentException("updateInterview() passed null Interview");
		}

		ContentValues values = valuesFromInterview(interview);
		String whereClause = EthnographySQLiteOpenHelper.COLUMN_ID + " = ?";
		String whereArgs[] = new String[1];
		whereArgs[0] = String.valueOf(interview.getID());
		int rows = db.update(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS, values, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Update on primary-key restraint updated more than one row");
		}

		return result;
	}

	boolean updateAudio(InterviewAudio audio) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		if (audio == null) {
			throw new IllegalArgumentException("addInterview() passed null Interview");
		}

		ContentValues values = valuesFromAudio(audio);
		String whereClause = EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ? and " +
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String whereArgs[] = new String[2];
		whereArgs[0] = audio.getFileName();
		whereArgs[1] = String.valueOf(audio.getInterviewID());
		int rows = db.update(EthnographySQLiteOpenHelper.TABLE_AUDIO, values, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Delete on primary-key restraint updated more than one row");
		} 
		return result;
	}

	boolean updatePhoto(InterviewPhoto photo) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		if (photo == null) {
			throw new IllegalArgumentException("addInterview() passed null Interview");
		}

		ContentValues values = valuesFromPhoto(photo);
		String whereClause = EthnographySQLiteOpenHelper.COLUMN_FILE_NAME + " = ? and " +
				EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String whereArgs[] = new String[2];
		whereArgs[0] = photo.getFileName();
		whereArgs[1] = String.valueOf(photo.getInterviewID());
		int rows = db.update(EthnographySQLiteOpenHelper.TABLE_PHOTO, values, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Update on primary-key restraint updated more than one row");
		} 
		return result;
	}

	/**
	 * Generates a {@link ContentValues} object for use with a database insert method
	 * from an {@link Interview} object. If {@code interview} is {@code null}, the
	 * returned object will represent a blanked row i.e. empty (not null) strings and
	 * 0 integers.
	 * @param interview The object from which to retrieve the data to insert. May be {@code null}
	 * @return A {@link ContentValues} object generated from the data.
	 */
	private static ContentValues valuesFromInterview(Interview interview) {
		ContentValues values = new ContentValues();
		if (interview == null) {
			values.put(EthnographySQLiteOpenHelper.COLUMN_STORY_TITLE, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_SUB_FIRST_NAME, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_SUB_LAST_NAME, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_INTER_FIRST_NAME, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_INTER_LAST_NAME, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_LOCATION_NAME, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, -1);
			values.put(EthnographySQLiteOpenHelper.COLUMN_NOTES, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_UPLOADED, false);
			values.put(EthnographySQLiteOpenHelper.COLUMN_MEDIA_PATH, "");
			values.put(EthnographySQLiteOpenHelper.COLUMN_SERVER_KEY, 0);
		} else {
			values.put(EthnographySQLiteOpenHelper.COLUMN_STORY_TITLE, interview.getStoryTitle());
			values.put(EthnographySQLiteOpenHelper.COLUMN_SUB_FIRST_NAME, interview.getSubjectName().getFirstName());
			values.put(EthnographySQLiteOpenHelper.COLUMN_SUB_LAST_NAME, interview.getSubjectName().getLastName());
			values.put(EthnographySQLiteOpenHelper.COLUMN_INTER_FIRST_NAME, interview.getInterviewerName().getFirstName());
			values.put(EthnographySQLiteOpenHelper.COLUMN_INTER_LAST_NAME, interview.getInterviewerName().getLastName());
			values.put(EthnographySQLiteOpenHelper.COLUMN_LOCATION_NAME, interview.getLocationName());
			if (interview.getDate() == null) {
				values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, -1);
				values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, -1);
				values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, -1);
			} else {
				Log.d(LOG_TAG, DateFormat.getDateInstance().format(interview.getDate()));
				Calendar calendar = new GregorianCalendar();
				calendar.setTime(interview.getDate());
				values.put(EthnographySQLiteOpenHelper.COLUMN_YEAR, calendar.get(Calendar.YEAR));
				values.put(EthnographySQLiteOpenHelper.COLUMN_MONTH, calendar.get(Calendar.MONTH));
				values.put(EthnographySQLiteOpenHelper.COLUMN_DAY, calendar.get(Calendar.DAY_OF_MONTH));
			}
			values.put(EthnographySQLiteOpenHelper.COLUMN_NOTES, interview.getNotes());
			values.put(EthnographySQLiteOpenHelper.COLUMN_UPLOADED, interview.isUploaded());
			values.put(EthnographySQLiteOpenHelper.COLUMN_MEDIA_PATH, interview.getMediaPath());
			values.put(EthnographySQLiteOpenHelper.COLUMN_SERVER_KEY, interview.getServerKey());
		}
		return values;
	}

	/**
	 * Deletes the row with the specified ID from the interviews table, and also deletes
	 * any entries with the specified ID from the audio and photos table
	 * 
	 * @param rowID The ID of the row to remove from the database.
	 * @return {@code true} if a row was deleted; {@code false} if not.
	 * @throws IllegalStateException if the database is not open.
	 */
	boolean deleteRow(int rowID) {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		String whereClause = EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String[] whereArgs = new String[1];
		whereArgs[0] = String.valueOf(rowID);

		db.delete(EthnographySQLiteOpenHelper.TABLE_AUDIO, whereClause, whereArgs);
		db.delete(EthnographySQLiteOpenHelper.TABLE_PHOTO, whereClause, whereArgs);

		whereClause = EthnographySQLiteOpenHelper.COLUMN_ID + " = ?";

		int rows = db.delete(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS, whereClause, whereArgs);
		boolean result;
		if (rows == 0) {
			result = false;
		} else if (rows == 1) {
			result = true;
		} else {
			// If this ever gets executed, there's a bug because an update on a primary-
			// key constraint should never update more than one row.
			throw new SQLiteConstraintException("Delete on primary-key restraint updated more than one row");
		} 
		return result;
	}

	/**
	 * Deletes all rows from the database that are "uploaded"
	 * @return The number of rows deleted
	 */
	int deleteUploaded() {
		if (db == null) {
			throw new IllegalStateException("Database not open");
		}
		String whereClause = EthnographySQLiteOpenHelper.COLUMN_UPLOADED + " = 1";
		int rows = db.delete(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS, whereClause, null);
		return rows; // Number of deleted rows
	}

	/**
	 * Retrieives all rows that have null strings for all fields except
	 * Media Path, are not marked as uploaded, and which do not have any
	 * associated media files.
	 * @return
	 */
	Collection<Interview> getBlank() {

		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_INTERVIEWS,
				allInterviewColumns,
				EthnographySQLiteOpenHelper.COLUMN_INTER_FIRST_NAME + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_INTER_LAST_NAME + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_SUB_FIRST_NAME + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_SUB_LAST_NAME + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_LOCATION_NAME + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_NOTES + " = '' and " +
						EthnographySQLiteOpenHelper.COLUMN_UPLOADED +" = 0",
						null,
						null,
						null,
						null);

		List<Interview> list = new ArrayList<Interview>();
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			int id = cursor.getInt(1);
			// Get the media files associated with the id, and add it to the list if
			// there are none
			Cursor audioCursor = getAudioByID(id);
			Cursor photoCursor = getPhotosByID(id);
			if (audioCursor.getCount() == 0 && photoCursor.getCount() == 0) {
				Interview i = new Interview();
				try {
					populateInterview(i, cursor);
				} catch (ParseException e) {
					throw new SQLiteException(e.getLocalizedMessage());
				}
				list.add(i);
			} else {
				cursor.moveToNext();
			}
		}
		return list;
	}

	private Cursor getAudioByID(int id) {
		String selection = EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String selectionArgs[] = new String[1];
		selectionArgs[0] = String.valueOf(id);
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_AUDIO, 
				allAudioColumns, 
				selection,
				selectionArgs, 
				null,
				null,
				null);

		return cursor;
	}

	private Cursor getPhotosByID(int id) {
		String selection = EthnographySQLiteOpenHelper.COLUMN_INTERVIEW_ID + " = ?";
		String selectionArgs[] = new String[1];
		selectionArgs[0] = String.valueOf(id);
		Cursor cursor = db.query(EthnographySQLiteOpenHelper.TABLE_PHOTO, 
				allPhotoColumns, 
				selection,
				selectionArgs, 
				null,
				null,
				null);

		return cursor;
	}
}
